﻿#include "unrealircd.h"

/*
此模块存放数据库的sql语句

*/



//数据库预备命令定义------------------------------------------

//------------------------- im_user 表 ------------------------- 

//根据用户id查询用户信息
DB_Command_Prepared db_query_imUser_by_id = {
	"SELECT im_user.\"id\" ,im_user.git_id, im_user.sex,im_user.user_name,im_user.user_password,im_user.tel,im_user.created_at,im_user.user_nick FROM im_user WHERE im_user.\"id\"=$1;",
	"query_imUser_by_id",
	1,
	{"int4"},
	0
};

//注册用户时，插入一条新的用户信息
DB_Command_Prepared db_insert_imUser = {
	"insert into im_user (\"id\",git_id,user_name,user_password,tel,sex,created_at,user_nick) VALUES(nextval('users_id_seq'),$1,$2,$3,$4,$5,$6,$7) RETURNING *;",
	"insert_imUser",
	7,
	{"int4","varchar","varchar","varchar","int2","timestamp","varchar"},
	0
};

//根据用户名查询用户信息
DB_Command_Prepared db_query_imUser_by_user_name = {
	"SELECT im_user.id ,im_user.git_id, im_user.sex,im_user.user_name,im_user.user_password,im_user.tel,im_user.created_at,im_user.user_nick FROM im_user WHERE im_user.user_name=$1;",
	"query_imUser_by_user_name",
	1,
	{"varchar"},
	0
};

//根据用户手机号查询用户信息
DB_Command_Prepared db_query_imUser_by_tel = {
	"SELECT im_user.id ,im_user.git_id, im_user.sex,im_user.user_name,im_user.user_password,im_user.tel,im_user.created_at,im_user.user_nick FROM im_user WHERE im_user.tel=$1;",
	"query_imUser_by_tel",
	1,
	{"varchar"},
	0
};

//删除一个用户
DB_Command_Prepared db_delete_imUser={
	"DELETE from im_user where im_user.id=$1 RETURNING *;",
	"db_delete_imUser",
	1,
	{"int4"},
	0
};

//更新用户信息
DB_Command_Prepared db_update_imUser = {
	"UPDATE im_user set git_id=$2 ,user_name=$3 ,user_password=$4 ,tel=$5 ,sex=$6 ,created_at=$7, user_nick=$8  WHERE \"id\"=$1 RETURNING *;",
	"db_update_imUser",
	8,
	{"int4", "int4", "varchar", "varchar", "varchar", "int2", "timestamp",  "varchar"},
	0
};

//根据用户id查询其好友的用户信息
DB_Command_Prepared db_query_imUserFriend_by_id = {
	"SELECT B.id ,B.git_id, B.sex,B.user_name,B.user_password,B.tel,B.created_at,A.nick_name user_nick  FROM im_user_relation  A INNER JOIN im_user B ON B.\"id\" = A.friend_id WHERE A.user_id=$1 ;",
	"db_query_imUserFriend_by_id",
	1,
	{"int4"},
	0
};

//------------------------- im_configure 表 ------------------------- 

//查询配置信息
DB_Command_Prepared db_query_imConfigure_by_configur_name={
	"SELECT configure_name ,configure_value,configure_detail FROM im_configure WHERE configure_name=$1;",
	"query_imConfigure_by_configur_name",
	1,
	{"varchar"},
	0
};




//------------------------- im_third_login 表 ------------------------- 

//查询第三方登陆openid
DB_Command_Prepared db_query_im_third_login_by_openid_and_thirdid={
	"SELECT third_id,open_id,user_id,nike_name FROM im_third_login WHERE open_id=$1 and third_id=$2",
	"query_im_third_login_by_openid_and_thirdid",
	2,
	{"varchar","int4"},
	0
};

//查询第三方登陆openid
DB_Command_Prepared db_query_im_third_login_by_userid_and_thirdid={
	"SELECT third_id,open_id,user_id,nike_name FROM im_third_login WHERE user_id=$1 and third_id=$2",
	"query_im_third_login_by_userid_and_thirdid",
	2,
	{"varchar","int4"},
	0
};

//查询第三方登陆openid
DB_Command_Prepared db_query_im_third_login_by_userid={
	"SELECT third_id,open_id,user_id,nike_name FROM im_third_login WHERE user_id=$1",
	"query_im_third_login_by_userid",
	1,
	{"varchar"},
	0
};

//插入一个第三方登录的openid
DB_Command_Prepared db_insert_im_third_login={
	"INSERT INTO im_third_login(third_id,open_id,nike_name,user_id) VALUES($1,$2,$3,$4) RETURNING *;",
	"insert_im_third_login",
	4,
	{"int4","varchar","varchar","int4"},
	0
};

// 根据用户id和第三方id删除一个记录
DB_Command_Prepared db_delete_im_third_login_by_userIdAndThirdId = {
	"DELETE  FROM im_third_login WHERE im_third_login.user_id=$1 and im_third_login.third_id=$2 RETURNING *;",
	"db_delete_im_third_login_by_userIdAndThirdId",
	2,
	{"int4","int4"},
	0
};


//------------------------- im_sms 表 ------------------------- 

//插入一个手机注册验证记录
DB_Command_Prepared db_insert_im_sms={
	"INSERT INTO im_sms(identity_no,contactvalue,sms_type,used,code,valid_time,sms_channel) VALUES(nextval('im_sms_seq'),$1,$2,$3,$4,$5,$6) RETURNING *;",
	"db_insert_im_sms",
	6,
	{"varchar","int2","int2","varchar","timestamp","int2"},
	0
};

//查询验证码
DB_Command_Prepared db_query_im_sms_by_type_and_contactvalue={
	"SELECT im_sms.identity_no,im_sms.sms_type ,im_sms.used ,im_sms.code ,im_sms.valid_time ,im_sms.contactvalue ,im_sms.sms_channel  FROM im_sms WHERE im_sms.used=0 and im_sms.sms_type=$1 and im_sms.contactvalue=$2 and im_sms.sms_channel=$3 and (im_sms.valid_time between $4 and $5) ORDER BY im_sms.valid_time DESC  LIMIT 1;",
	"db_query_im_sms_by_type_and_contactvalue",
	5,
	{"int2","varchar","int2","timestamp","timestamp"},
	0
};

//更新验证码
DB_Command_Prepared db_updata_im_sms_by_identity_no ={
	"UPDATE im_sms set sms_type=$2 ,used=$3 ,code=$4 ,valid_time=$5 ,contactvalue=$6 ,sms_channel=$7  WHERE identity_no=$1 RETURNING * ;",
	"db_updata_im_sms_by_identity_no",
	7,
	{"int4","int2","int2","varchar","timestamp","varchar","int2"},
	0
};






//------------------------- im_user_email 表 ------------------------- 

//插入一个用户邮箱记录
DB_Command_Prepared db_insert_im_user_email={
	"INSERT INTO im_user_email(id,email,permition) VALUES($1,$2,$3) RETURNING *;",
	"db_insert_im_user_email",
	3,
	{"int4","varchar","int4"},
	0
};

//更新用户邮箱记录
DB_Command_Prepared db_update_im_user_email = {
	"UPDATE im_user_email set \"id\"=$1 ,email=$2 ,permition=$3 WHERE \"id\"=$1 and email = $2 RETURNING *;",
	"db_update_im_user_email",
	3,
	{"int4","varchar","int4"},
	0
};

//根据用户邮箱查询用户邮箱信息
DB_Command_Prepared db_query_im_user_email_by_email = {
	"SELECT id ,email, permition FROM im_user_email WHERE im_user_email.email=$1;",
	"db_query_im_user_email_by_email",
	1,
	{"varchar"},
	0
};

//根据用户id查询邮箱信息
DB_Command_Prepared db_query_im_user_email_by_id = {
	"SELECT id ,email, permition FROM im_user_email WHERE im_user_email.id=$1;",
	"db_query_im_user_email_by_id",
	1,
	{"int4"},
	0
};

//根据用户邮箱和权限查询邮箱信息
DB_Command_Prepared db_query_im_user_email_by_idAndPermition = {
	"SELECT id ,email, permition FROM im_user_email WHERE im_user_email.id=$1 and (im_user_email.permition & $2)>0;",
	"db_query_im_user_email_by_idAndPermition",
	2,
	{"int4","int4"},
	0
};

//根据用户邮箱和权限查询邮箱信息
DB_Command_Prepared db_query_im_user_email_by_emailAndPermition = {
	"SELECT id ,email, permition FROM im_user_email WHERE im_user_email.email=$1 and (im_user_email.permition & $2)>0;",
	"db_query_im_user_email_by_emailAndPermition",
	2,
	{"int4","int4"},
	0
};

//根据用户邮箱和id查询邮箱信息
DB_Command_Prepared db_query_im_user_email_by_idAndEmail = {
	"SELECT id ,email, permition FROM im_user_email WHERE im_user_email.id=$1 and im_user_email.email=$2;",
	"db_query_im_user_email_by_idAndEmail",
	2,
	{"int4","varchar"},
	0
};

// 根据邮箱号删除用户邮箱信息
DB_Command_Prepared db_delete_im_user_email_by_idAndEmail = {
	"DELETE  FROM im_user_email WHERE im_user_email.id=$1 and im_user_email.email=$2 RETURNING *;",
	"db_delete_im_user_email_by_idAndEmail",
	2,
	{"int4","varchar"},
	0
};

//------------------------- im_login_record 表 ------------------------- 

// 插入一条登录的记录
DB_Command_Prepared db_insert_im_login_record = {
	"INSERT INTO im_login_record(login_no, user_id, log_time, login_ip, login_mac) VALUES(nextval('login_record_id_seq'),$1,$2,$3,$4) RETURNING *;",
	"db_insert_im_login_record",
	4,
	{"int4","timestamp","varchar","varchar"},
	0
};


//------------------------- im_relation_applicant 表 ------------------------- 
DB_Command_Prepared db_insert_im_relation_applicant = {
	"INSERT INTO im_relation_applicant(relation_applicant_id, applicant_type, applicant_id, respondent_id, project_group_id, applicant_status, applicant_time) VALUES(nextval('relation_applicant_seq'),$1,$2,$3,$4,$5,$6) RETURNING *;",
	"db_insert_im_relation_applicant",
	6,
	{"int4","int4","int4","int4","int4","timestamp"},
	0
};

DB_Command_Prepared db_update_im_relation_applicant = {
	"UPDATE im_relation_applicant set applicant_type=$2 ,applicant_id=$3 ,respondent_id=$4, project_group_id=$5, applicant_status=$6  WHERE relation_applicant_id=$1 RETURNING *;",
	"db_update_im_relation_applicant",
	6,
	{"int4","int4","int4","int4","int4","int4"},
	0
};

DB_Command_Prepared db_query_im_relation_applicant_by_respondentId = {
	"SELECT relation_applicant_id, applicant_type, applicant_id, respondent_id, project_group_id, applicant_status, applicant_time FROM im_relation_applicant WHERE respondent_id=$1 and (applicant_time BETWEEN $2 AND $3);",
	"db_query_im_relation_applicant_by_respondentId",
	3,
	{"int4","timestamp","timestamp"},
	0
};

DB_Command_Prepared db_query_im_relation_applicant_by_applicantId = {
	"SELECT relation_applicant_id, applicant_type, applicant_id, respondent_id, project_group_id, applicant_status, applicant_time FROM im_relation_applicant WHERE applicant_id=$1 and (applicant_time BETWEEN $2 AND $3);",
	"db_query_im_relation_applicant_by_applicantId",
	3,
	{"int4","timestamp","timestamp"},
	0
};

DB_Command_Prepared db_query_im_relation_applicant_by_relationApplicantId = {
	"SELECT relation_applicant_id, applicant_type, applicant_id, respondent_id, project_group_id, applicant_status, applicant_time FROM im_relation_applicant WHERE relation_applicant_id=$1 and (applicant_time BETWEEN $2 AND $3);",
	"db_query_im_relation_applicant_by_relationApplicantId",
	3,
	{"int4","timestamp","timestamp"},
	0
};

//------------------------- im_user_relation 表 ------------------------- 

DB_Command_Prepared db_insert_im_user_relation = {
	"INSERT INTO im_user_relation(user_id, friend_id, nick_name) VALUES($1,$2,$3) RETURNING *;",
	"db_insert_im_user_relation",
	3,
	{"int4","int4","varchar"},
	0
};

DB_Command_Prepared db_update_im_user_relation = {
	"UPDATE im_user_relation set nick_name=$3  WHERE user_id=$1 and friend_id=$2 RETURNING *;",
	"db_update_im_user_relation",
	3,
	{"int4","int4","varchar"},
	0
};

DB_Command_Prepared db_query_im_user_relation_by_UserIdAndFriendId = {
	"SELECT user_id, friend_id, nick_name FROM im_user_relation WHERE user_id=$1 and friend_id=$2;",
	"db_query_im_user_relation_by_UserIdAndFriendId",
	2,
	{"int4","int4"},
	0
};

DB_Command_Prepared db_query_im_user_relation_by_UserId = {
	"SELECT user_id, friend_id, nick_name FROM im_user_relation WHERE user_id=$1;",
	"db_query_im_user_relation_by_UserId",
	1,
	{"int4"},
	0
};
